﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace nSL
{
    public class Query
    {
        
        public static string SQLConnectionString(string server,string user,string pass,string catalog)
        {
            return string.Format("Data Source={0}; uid={1}; pwd={2}; Initial Catalog={3};",server,user,pass,catalog);
        }

        public static string Login(string ticketID,string pwd="")
        {
            string sqlLogin = "SELECT T.TicketID,M.MemberName,S.SetCourseName ";
            sqlLogin += " FROM Fact_LearningTicket T,Dim_Member M,Dim_SetCourse S";
            sqlLogin += " WHERE TicketID = '{0}' ";
            sqlLogin += " AND T.MemberID = M.MemberID";
            sqlLogin += " AND T.SetCourseID = S.SetCourseID";
            sqlLogin += " AND T.MemberID <> 0 ";
            sqlLogin += " AND T.SetCourseID <> 0";
            return string.Format(sqlLogin, ticketID);
        }

        public static string GetCourse(string ticket)
        {
            string sql = @"SELECT DC.CourseName,DL.* 
                        FROM Dim_Lecture DL,Dim_Courses DC 
                        WHERE DC.CourseID = DL.CourseID 
                        AND DC.Showst = '1'
                        AND DL.Showst = '1'
                        AND DL.CourseID IN 
	                        (SELECT DC.CourseID
	                        FROM Dim_Courses DC
	                        WHERE DC.CourseID IN (SELECT DISTINCT FCS.CourseID 
						                        FROM Fact_CourseAndSetcourse FCS,Fact_LearningTicket FL
						                        WHERE FCS.SetCourseID = FL.SetCourseID
						                        AND FL.TicketID = '{0}'))";
            return string.Format(sql, ticket);
        }

        public static string GetFile(string ticky)
        {
            string sql = @"SELECT * 
                        FROM Dim_Video DV
                        WHERE DV.LectureID IN (
	                        SELECT DL.LectureID
	                        FROM Dim_Lecture DL
	                        WHERE DL.CourseID IN 
		                        (SELECT DC.CourseID
		                        FROM Dim_Courses DC
		                        WHERE DC.CourseID IN (SELECT DISTINCT FCS.CourseID 
							                        FROM Fact_CourseAndSetcourse FCS,Fact_LearningTicket FL
							                        WHERE FCS.SetCourseID = FL.SetCourseID
							                        AND FL.TicketID = '{0}'))
	                        AND DL.Showst = '1')
                        AND DV.showst ='1'";
            return string.Format(sql, ticky);
        }
    }
}
